# -*- coding : utf-8 -*-
# @Time    : 2023/12/07 17:20
# @Author  : wanggang
# @Email   : wanggang2@lingxi.ai
# @File    : hologres_entity.py
# @Software: LLM_internal

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #


import sys
# sys.path.append('../')
import tqdm
import json
import pandas as pd
import logging
import datetime
import requests
import psycopg2
import threadpool
import traceback

from openpyxl import load_workbook, Workbook


pool = threadpool.ThreadPool(50)

def get_customer_all_dialogue(min_date, max_date):
    connection = psycopg2.connect(
        host="hgprecn-cn-2r42t31q3003-cn-zhangjiakou.hologres.aliyuncs.com",
        port=80,
        user="LTAI5tMsnFz7WRxKJyBDK5uv",
        password="SZxfgbrWpDYaHOR4HsU5ENxDOw6Xxt",
        dbname="screen"
    )

    cursor = connection.cursor()

    call1_task_id = [523, 1376, 1378, 1447, 1459, 1460, 1462, 1360, 1384, 1447, ]
    call2_task_id = [171, 1259, 1379, 1381, 1385, 1448, 1461, ]

    sql = '''
    with A as (
            select customer_id, dm_session_id, call_task_id, answer_duration, dm_version, call_start_time 
            from screen.public.outbound_call_result 
            where call_start_time > '{} 09:00:00' 
            and call_start_time < '{} 23:00:00' 
            and answer_time is not null 
            and dm_session_id is not null 
            and customer_id is not null 
            and company_id = 2144 
            and call_task_id in (523, 1376, 1378, 1447, 1459, 1460, 1462, 1360, 1384, 1447)),
         B as (
            select customer_id, json_data 
            from public.outbound_call_case
            where company_id = 2144 
            and last_call_time > '{} 09:00:00' 
         )

    select A.customer_id, B.customer_id, 
    A.dm_session_id, A.answer_duration, A.dm_version, A.call_start_time, B.json_data 
    from A left join B on A.customer_id = B.customer_id 
    where A.customer_id = B.customer_id 
    '''

    cursor.execute(sql.format(min_date, max_date, min_date))
    results = cursor.fetchall()

    url = 'http://172.26.2.56:8630/report/getDetailedRecord/?sessionId={}'
    # url = 'http://8.142.85.77:8630/report/getDetailedRecord/?sessionId={}'

    wb = Workbook()
    ws = wb.active
    ws.append(['用户ID', '拨打时间', 'session_id', '通话时长', '对话记录', '用户权益'])
    customer_id_dict = dict()
    customer_info_dict = dict()
    parameter_list = list()
    for result in tqdm.tqdm(results):
        # print(result)
        customer_id = result[0]
        session_id = result[2]
        answer_duration = result[3]
        dm_version = result[4]
        call_start_time = result[5]
        json_data = result[6]
        if customer_id not in customer_id_dict:
            customer_id_dict[customer_id] = 0
        customer_id_dict[customer_id] += 1

        parameter_list.append((
            [customer_id, session_id, answer_duration, dm_version, call_start_time, json_data, ws], None
        ))

    request = threadpool.makeRequests(multi_task, parameter_list)
    [pool.putRequest(req) for req in request]
    pool.wait()

    wb.save('./{}.xlsx'.format(max_date))

    for customer_id in customer_id_dict:
        if customer_id_dict[customer_id] > 1:
            print(customer_id)

    print(len(results))


def get_base_info(user_info):
    name = ''
    age = ''
    certificate = ''
    right = '无权益'
    rightCutRate = ''
    try:
        if not user_info:
            return {'用户姓名': name, '用户年龄': age, '初始用户权益': right, '券信息': certificate,
                    '降息幅度': rightCutRate}
        name = user_info.get('fullName', '')
        sex = user_info.get('sexByChineseWord', '')
        if name and sex:
            name = name + sex
        else:
            name = ''
        age = user_info.get('ageRange', '')
        certificate = ''
        low_interest = False
        coupon = False
        withdrawal = False

        couponDetailJson = user_info.get('couponDetailList', '')
        if couponDetailJson:
            couponDetailList = json.loads(couponDetailJson)
            for couponDetail in couponDetailList:
                activityName = couponDetail.get('activityName', '')
                if activityName:
                    if '免息' in activityName and '券' in activityName:
                        certificate = activityName
                        break
                    elif '券' in activityName:
                        certificate = activityName
                        continue

        cutRateTime = user_info.get('cutRateTime', '')
        try:
            if cutRateTime and cutRateTime != '-' and datetime.datetime.strptime(cutRateTime, '%Y-%m-%d'):
                cutRateTime = datetime.datetime.strptime(cutRateTime, '%Y-%m-%d')
                current_date = datetime.datetime.now()
                days_left = (cutRateTime - current_date).days
                if days_left >= 0:
                    cutRate = user_info.get('cutRate', '')
                    if cutRate and cutRate == '是':
                        low_interest = True
                    # 降息前
                    beforeCutRate = user_info.get('beforeCutRate', '')
                    # 降息后
                    currentRate = user_info.get('currentRate', '')

                    beforeCutRate = beforeCutRate.replace('-', '')
                    currentRate = currentRate.replace('-', '')

                    if beforeCutRate and currentRate:
                        # 当前费率是这个 同一都是低
                        if '0.00015 ~ 0.00025' == currentRate:
                            rightCutRate = '利息降到最低'
                        # 当前费率
                        if '0.00025 ~ 0.00035' == currentRate and (
                                '0.00045 ~ 0.00055' == beforeCutRate or '0.00055 ~ 0.00065' == beforeCutRate):
                            rightCutRate = '利息降幅大'
                        if '0.00035 ~ 0.00045' == currentRate and '0.00055 ~ 0.00065' == beforeCutRate:
                            rightCutRate = '利息降幅大'

        except Exception:
            traceback.print_exc()
            logging.error('获取降息权益错误')

        couponName = user_info.get('couponName', '')
        if couponName or certificate:
            coupon = True

        try:
            tradeTime = user_info.get('tradeTime', '')
            if tradeTime and datetime.datetime.strptime(tradeTime, '%Y-%m-%d'):
                withdrawal = True
        except Exception:
            traceback.print_exc()
            logging.error('日期转换错误')
        rightList = list()
        if low_interest:
            rightList.append('已获得降息')
        if withdrawal:
            rightList.append('已获得提额')
        if coupon:
            rightList.append('已获得优惠券')
        if not rightList:
            rightList.append('无权益')
        right = ','.join(rightList)
        return {'用户姓名': name, '用户年龄': str(age), '初始用户权益': right, '券信息': certificate,
                '降息幅度': rightCutRate}
    except Exception:
        return {'用户姓名': name, '用户年龄': str(age), '初始用户权益': right, '券信息': certificate,
                '降息幅度': rightCutRate}



def multi_task(customer_id, session_id, answer_duration, dm_version, call_start_time, json_data, ws):
    url = 'http://172.26.2.56:8630/report/getDetailedRecord/?sessionId={}'

    name = ''
    dialogue_information = ''
    response = requests.get(url.format(session_id)).text
    res = json.loads(response)['result'] if json.loads(response)['result'] else list()
    conversation_history = list()
    for j in range(len(res)):
        try:
            # 第一句的生成不是由大模型产生的
            if res[j]['speakerType'] == 'IVR':
                content = res[j]['msgContent']

                if '先生' in content:
                    name = content.split('先生')[0][-1] + '先生'
                elif '女士' in content:
                    name = content.split('女士')[0][-1] + '女士'

                if dm_version == 'LLM版本':
                    content = content.split('#@')[-1].split('@@')[0]
                else:
                    content_list = list()
                    for con in content.split('@#')[1:]:
                        content_list.append(con.split('#@')[0])
                    content = ''.join(content_list).split('||')[-1]
                conversation_history.append('销售员:' + content)

                # 通过历史对话记录获取对话的各种信息
                if 'llmResult' in res[j]:
                    user_question = res[j]['llmResult']['intention'][0]['user_question']
                    if '在忙' in user_question:
                        dialogue_information = '之前在忙'
                    elif '不需要' in user_question:
                        dialogue_information = '之前不需要'


            elif res[j]['speakerType'] == 'USER':
                content = res[j]['msgContent'].split(']')[-1]
                conversation_history.append('用户:' + content)

                for slot in json.loads(res[j]['idlResultJson'])['slot']:
                    if slot['slotName'] == 'FAQ意图':
                        FAQ_name = slot['slotValue'][0]
                        if '在忙' in FAQ_name:
                            dialogue_information = '之前在忙'
                        elif '不需要' in FAQ_name:
                            dialogue_information = '之前不需要'

        except Exception as err:
            pass

    base_info = get_base_info(json.loads(json_data))
    base_info['用户姓名'] = name

    ws.append([
        customer_id, str(call_start_time), session_id, answer_duration, '\n'.join(conversation_history),
        str(base_info), dialogue_information
    ])


def star_2132():

    connection = psycopg2.connect(
        host="hgprecn-cn-2r42t31q3003-cn-zhangjiakou.hologres.aliyuncs.com",
        port=80,
        user="LTAI5tMsnFz7WRxKJyBDK5uv",
        password="SZxfgbrWpDYaHOR4HsU5ENxDOw6Xxt",
        dbname="screen"
    )

    cursor = connection.cursor()

    sql = '''
    with A as (
        select occ.id as case_id, occ.customer_id, oce.data_star
        from (
            select *
            from outbound_call_case
            where company_id = 2132
            and update_time > current_date - 10 
        )occ
        join (
            select case_id, data_star
            from outbound_call_case_ops_extend
        ) oce
        on occ.id = oce.case_id),
         B as (
            select customer_id, score_json 
            from public.outbound_call_case
            where company_id = 2132  
            and last_call_time > '2023-12-14 09:00:00' 
            and last_call_time < '2023-12-14 23:00:00'
         )
         
    select A.case_id, A.data_star, B.*
    from A left join B on A.customer_id = B.customer_id 
    where A.customer_id = B.customer_id 
    '''

    sql = '''
    select customer_id, dm_session_id, company_id, case_id, call_status, call_type, result_id, workspace_id, call_start_time  
    from public.outbound_call_result 
    where company_id = 2132  
    and call_start_time > '2023-12-18 10:00:00' 
    and call_start_time < '2023-12-18 12:00:00'  
    and dm_version in ('JT-实分转-希爱2call-20231114', 
        'JT-实时-实分转-淑婷-031109新版A短信-带操作',
        'JT-实时批次-淑婷-231212',
        'JT-未-2call实转-淑婷-20230612去短信',
        'T-实时-实分转-淑婷-231206新版A短信-带操作') 
    and call_status in ('normalConnection', 'transferFail')
    '''

    cursor.execute(sql)
    results = cursor.fetchall()
    connection.close()

    print(len(results))
    star_dict = {
        '0': 0,
        '1': 0,
        '2': 0,
        '3': 0,
        '4': 0,
    }
    tag_dict = dict()
    label_set = set()
    parameter_list = list()
    session_id_list = list()

    wb_re = Workbook()
    ws_re = wb_re.active
    ws_re.append([
        '用户ID', 'session_id', 'company_id', 'case_id', 'call_status', 'call_type', 'result_id', 'workspace_id'
    ])

    for result in tqdm.tqdm(results):
        '''if result[3] != None:
            print(result)
            if result[1]:
                star_dict[str(result[1])] += 1
            else:
                star_dict['0'] += 1'''
        # print(result)
        session_id = result[1]
        parameter_list.append((
            [session_id, tag_dict, label_set, session_id_list, result, ws_re], None
        ))

    request = threadpool.makeRequests(star_task, parameter_list)
    [pool.putRequest(req) for req in request]
    pool.wait()

    df = pd.read_csv('./df_features_all.csv')
    all_num = 0
    tag_dict = dict(sorted(tag_dict.items(), key=lambda item: item[1], reverse=True))

    for tag in tag_dict:
        all_num += tag_dict[tag]

    wb = Workbook()
    ws = wb.active
    ws.append([
        '名字', '数量', '占比', '新老', '是否同时出现'
    ])
    for tag in tag_dict:
        # print(tag, tag_dict[tag], tag_dict[tag] / all_num, '不存在' if tag not in list(df.columns) else '存在')
        ws.append([
            tag, tag_dict[tag], tag_dict[tag] / all_num, '不存在' if tag not in list(df.columns) else '存在',
            '重复' if tag in label_set else '不重复'
        ])
    num = 0
    for old_tag in tqdm.tqdm(list(df.columns)):
        if old_tag not in tag_dict.keys():
            print(old_tag)
            num += 1
    print(num)

    ws = wb.create_sheet('与FAQ同时出现标签')
    for label in label_set:
        ws.append([label])

    wb.save('./结果.xlsx')
    wb_re.save('./重新打分数据.xlsx')

    # print(session_id_list)





def star_task(session_id, tag_dict, label_set, session_id_list, result, ws_re):
    url = 'http://8.142.85.77:8630/report/getDetailedRecord/?sessionId={}'

    response = requests.get(url.format(session_id)).text
    res = json.loads(response)['result'] if json.loads(response)['result'] else list()
    sign = False
    for j in range(len(res)):

        try:
            if res[j]['speakerType'] == 'IVR':

                for tag in res[j]['contentLabel']:
                    tag = tag.replace("FAQ:", "") \
                        .replace('#complain#', "投诉").replace('开场', "") \
                        .replace('产介', "").replace('否定态度', "").replace('实时IVR', "") \
                        .replace('不需要无原因两次', "不需要两次").replace('不需要其他两次', "不需要两次")

                    if tag in ['用户主动表示:有需要再联系', '用户主动表示:客户会操作', '用户主动表示:要求挂机', '用户主动询问:产品费用', '用户主动表示:你找谁',
                               '坐席询问用户:是否有京东APP-有京东APP', '坐席询问用户:是否有京东APP-用户否定',
                               '坐席询问用户:是否有京东APP-用户无明确回应', '坐席询问用户:是否有京东APP-用户肯定']:
                        sign = True

                    # 同时出现标签
                    if tag.startswith('坐席'):
                        dialogue_label = '-'.join(tag.split('-')[1:]).replace('-首句', '').replace('首句', '')
                        if dialogue_label and dialogue_label not in ['用户肯定', '用户否定', '用户无明确回应'] and \
                                (dialogue_label in res[j]['contentLabel'] or 'FAQ:'+dialogue_label in res[j]['contentLabel']):
                            label_set.add(tag)

                    elif tag.startswith('用户') or '轮询' in tag:
                        dialogue_label = tag.replace('轮询', '').replace('用户主动询问:', '').replace('用户主动表示:', '')
                        if dialogue_label and (dialogue_label in res[j]['contentLabel'] or \
                                               'FAQ:'+dialogue_label in res[j]['contentLabel']):
                            label_set.add(tag)



                    if tag not in tag_dict:
                        tag_dict[tag] = 0
                    tag_dict[tag] += 1


        except Exception as err:
            pass

    if sign:
        connection = psycopg2.connect(
            host="hgprecn-cn-2r42t31q3003-cn-zhangjiakou.hologres.aliyuncs.com",
            port=80,
            user="LTAI5tMsnFz7WRxKJyBDK5uv",
            password="SZxfgbrWpDYaHOR4HsU5ENxDOw6Xxt",
            dbname="screen"
        )

        cursor = connection.cursor()
        sql = '''
        select customer_id, score_json, last_call_time 
        from public.outbound_call_case
        where company_id = 2132  
        and customer_id = '{}'  
        '''.format(result[0])
        cursor.execute(sql)
        res = cursor.fetchall()
        connection.close()
        score_json = res[0][1]
        last_call_time = res[0][2]

        session_id_list.append(session_id)
        ws_re.append([
            result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7], str(result[8]),
            str(score_json), str(last_call_time)
        ])



if __name__ == '__main__':

    # print( datetime.today() )

    '''min_date = datetime.date.today() - datetime.timedelta(days=1)
    max_date = datetime.date.today() - datetime.timedelta(days=1)

    print(min_date, max_date)
    get_customer_all_dialogue(min_date, max_date)'''

    for i in range(1, 2):
        date = datetime.date.today() - datetime.timedelta(days=i)
        print(date)
        get_customer_all_dialogue(date, date)

    # star_2132()

    a = {
        '用户主动表示:有需要再联系': '用户主动表示:客户会考虑',
        '用户主动表示:客户会操作': '用户主动表示:客户会考虑',
        '用户主动表示:要求挂机': '用户主动表示:投诉',
        '用户主动询问:产品费用': '用户主动询问:费用问题',
        '用户主动表示:你找谁': '用户主动询问:什么平台',

        '坐席询问用户:是否有京东APP-有京东APP': '坐席询问用户:是否有APP-用户肯定',
        '坐席询问用户:是否有京东APP-用户否定': '坐席询问用户:是否有APP-用户否定',
        '坐席询问用户:是否有京东APP-用户无明确回应': '坐席询问用户:是否有APP-用户无明确回应',
        '坐席询问用户:是否有京东APP-用户肯定': '坐席询问用户:是否有APP-用户肯定',
    }




